import sqlite3
import os
import hashlib
from datetime import datetime
from .config import get_database_path, get_db_connection_params, get_cache_size

class DatabaseManager:
    """数据库管理类，负责与SQLite数据库的交互"""
    
    def __init__(self, db_path=None):
        """初始化数据库连接"""
        # 使用配置的数据库路径，如果没有指定则使用默认路径
        self.db_path = db_path or get_database_path()
        
        # 确保数据库目录存在
        db_dir = os.path.dirname(self.db_path)
        if db_dir and not os.path.exists(db_dir):
            os.makedirs(db_dir)
            
        self.conn = None
        self.cursor = None
        
        # 初始化数据库
        self.init_database()
    
    def connect(self):
        """建立数据库连接"""
        if self.conn is None or self.conn.closed:
            # 获取连接参数
            conn_params = get_db_connection_params()
            
            # 建立连接
            self.conn = sqlite3.connect(self.db_path, **conn_params)
            self.cursor = self.conn.cursor()
            
            # 启用外键约束
            self.cursor.execute("PRAGMA foreign_keys = ON")
            
            # 设置缓存大小
            cache_size = get_cache_size()
            self.cursor.execute(f"PRAGMA cache_size = -{cache_size}")
            
            # 启用WAL模式以提高并发性能
            self.cursor.execute("PRAGMA journal_mode = WAL")
    
    def disconnect(self):
        """关闭数据库连接"""
        if self.conn is not None and not self.conn.closed:
            self.conn.close()
            self.conn = None
            self.cursor = None
    
    def init_database(self):
        """初始化数据库，创建所需的表"""
        self.connect()
        
        # 创建歌曲表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS songs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_name TEXT NOT NULL,
            file_path TEXT NOT NULL,
            file_format TEXT NOT NULL,
            file_size INTEGER NOT NULL,
            file_modified DATETIME NOT NULL,
            song_name TEXT,
            artist TEXT,
            album TEXT,
            duration INTEGER,
            bitrate INTEGER,
            file_hash TEXT,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(file_path)
        )
        ''')
        
        # 创建词条表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS tags (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tag_name TEXT NOT NULL UNIQUE
        )
        ''')
        
        # 创建歌曲-词条关联表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS song_tags (
            song_id INTEGER,
            tag_id INTEGER,
            PRIMARY KEY (song_id, tag_id),
            FOREIGN KEY (song_id) REFERENCES songs (id) ON DELETE CASCADE,
            FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
        )
        ''')
        
        # 创建索引以提高查询性能
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_songs_file_path ON songs (file_path)")
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_songs_song_name ON songs (song_name)")
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_songs_artist ON songs (artist)")
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_songs_album ON songs (album)")
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_songs_file_hash ON songs (file_hash)")
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_tags_tag_name ON tags (tag_name)")
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_song_tags_song_id ON song_tags (song_id)")
        self.cursor.execute("CREATE INDEX IF NOT EXISTS idx_song_tags_tag_id ON song_tags (tag_id)")
        
        self.conn.commit()
        self.disconnect()
    
    def add_song(self, file_info, metadata=None):
        """添加歌曲到数据库"""
        self.connect()
        
        try:
            # 检查文件是否已存在
            self.cursor.execute("SELECT id FROM songs WHERE file_path = ?", (file_info['file_path'],))
            existing_song = self.cursor.fetchone()
            
            if existing_song:
                # 更新现有歌曲
                update_data = {
                    'file_name': file_info['file_name'],
                    'file_format': file_info['file_format'],
                    'file_size': file_info['file_size'],
                    'file_modified': file_info['file_modified'],
                    'updated_at': datetime.now().isoformat()
                }
                
                # 添加元数据
                if metadata:
                    update_data.update(metadata)
                
                # 构建更新语句
                update_fields = ', '.join([f"{key} = ?" for key in update_data.keys()])
                update_values = list(update_data.values()) + [existing_song[0]]
                
                self.cursor.execute(f"UPDATE songs SET {update_fields} WHERE id = ?", update_values)
                song_id = existing_song[0]
            else:
                # 插入新歌
                insert_data = {
                    'file_name': file_info['file_name'],
                    'file_path': file_info['file_path'],
                    'file_format': file_info['file_format'],
                    'file_size': file_info['file_size'],
                    'file_modified': file_info['file_modified'],
                    'created_at': datetime.now().isoformat(),
                    'updated_at': datetime.now().isoformat()
                }
                
                # 添加元数据
                if metadata:
                    insert_data.update(metadata)
                
                # 构建插入语句
                columns = ', '.join(insert_data.keys())
                placeholders = ', '.join(['?' for _ in insert_data.keys()])
                
                self.cursor.execute(f"INSERT INTO songs ({columns}) VALUES ({placeholders})", list(insert_data.values()))
                song_id = self.cursor.lastrowid
            
            self.conn.commit()
            return song_id
        except sqlite3.Error as e:
            print(f"添加歌曲到数据库时出错: {e}")
            self.conn.rollback()
            return None
        finally:
            self.disconnect()
    
    def add_tag(self, tag_name):
        """添加词条到数据库"""
        self.connect()
        
        try:
            # 检查词条是否已存在
            self.cursor.execute("SELECT id FROM tags WHERE tag_name = ?", (tag_name,))
            existing_tag = self.cursor.fetchone()
            
            if existing_tag:
                return existing_tag[0]
            else:
                # 插入新词条
                self.cursor.execute("INSERT INTO tags (tag_name) VALUES (?)", (tag_name,))
                self.conn.commit()
                return self.cursor.lastrowid
        except sqlite3.Error as e:
            print(f"添加词条到数据库时出错: {e}")
            self.conn.rollback()
            return None
        finally:
            self.disconnect()
    
    def add_song_tag(self, song_id, tag_id):
        """为歌曲添加词条"""
        self.connect()
        
        try:
            # 检查关联是否已存在
            self.cursor.execute("SELECT 1 FROM song_tags WHERE song_id = ? AND tag_id = ?", (song_id, tag_id))
            existing = self.cursor.fetchone()
            
            if not existing:
                self.cursor.execute("INSERT INTO song_tags (song_id, tag_id) VALUES (?, ?)", (song_id, tag_id))
                self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"添加歌曲词条关联时出错: {e}")
            self.conn.rollback()
            return False
        finally:
            self.disconnect()
    
    def get_song_by_path(self, file_path):
        """通过文件路径获取歌曲信息"""
        self.connect()
        
        try:
            self.cursor.execute("SELECT * FROM songs WHERE file_path = ?", (file_path,))
            song = self.cursor.fetchone()
            if song:
                # 将结果转换为字典
                columns = [desc[0] for desc in self.cursor.description]
                return dict(zip(columns, song))
            return None
        except sqlite3.Error as e:
            print(f"获取歌曲信息时出错: {e}")
            return None
        finally:
            self.disconnect()
    
    def get_songs_by_tag(self, tag_name):
        """通过词条获取歌曲列表"""
        self.connect()
        
        try:
            self.cursor.execute('''
            SELECT s.* FROM songs s
            JOIN song_tags st ON s.id = st.song_id
            JOIN tags t ON st.tag_id = t.id
            WHERE t.tag_name = ?
            ''', (tag_name,))
            
            songs = self.cursor.fetchall()
            if songs:
                columns = [desc[0] for desc in self.cursor.description]
                return [dict(zip(columns, song)) for song in songs]
            return []
        except sqlite3.Error as e:
            print(f"通过词条获取歌曲列表时出错: {e}")
            return []
        finally:
            self.disconnect()
    
    def get_tags_by_song(self, song_id):
        """通过歌曲获取词条列表"""
        self.connect()
        
        try:
            self.cursor.execute('''
            SELECT t.tag_name FROM tags t
            JOIN song_tags st ON t.id = st.tag_id
            WHERE st.song_id = ?
            ''', (song_id,))
            
            tags = self.cursor.fetchall()
            return [tag[0] for tag in tags]
        except sqlite3.Error as e:
            print(f"通过歌曲获取词条列表时出错: {e}")
            return []
        finally:
            self.disconnect()
    
    def search_songs(self, query):
        """搜索歌曲"""
        self.connect()
        
        try:
            search_term = f"%{query}%"
            self.cursor.execute('''
            SELECT * FROM songs
            WHERE song_name LIKE ? OR artist LIKE ? OR album LIKE ? OR file_name LIKE ?
            ''', (search_term, search_term, search_term, search_term))
            
            songs = self.cursor.fetchall()
            if songs:
                columns = [desc[0] for desc in self.cursor.description]
                return [dict(zip(columns, song)) for song in songs]
            return []
        except sqlite3.Error as e:
            print(f"搜索歌曲时出错: {e}")
            return []
        finally:
            self.disconnect()
    
    def get_duplicate_songs(self, by_hash=True, by_name=False):
        """获取重复的歌曲"""
        self.connect()
        
        try:
            if by_hash:
                # 按哈希值查找重复歌曲
                self.cursor.execute('''
                SELECT file_hash, GROUP_CONCAT(id) as song_ids, COUNT(*) as count
                FROM songs
                WHERE file_hash IS NOT NULL AND file_hash != ''
                GROUP BY file_hash
                HAVING COUNT(*) > 1
                ''')
            elif by_name:
                # 按歌名查找重复歌曲
                self.cursor.execute('''
                SELECT song_name, GROUP_CONCAT(id) as song_ids, COUNT(*) as count
                FROM songs
                WHERE song_name IS NOT NULL AND song_name != ''
                GROUP BY song_name
                HAVING COUNT(*) > 1
                ''')
            
            duplicates = self.cursor.fetchall()
            return duplicates
        except sqlite3.Error as e:
            print(f"获取重复歌曲时出错: {e}")
            return []
        finally:
            self.disconnect()
    
    def delete_song(self, song_id):
        """删除歌曲"""
        self.connect()
        
        try:
            self.cursor.execute("DELETE FROM songs WHERE id = ?", (song_id,))
            self.conn.commit()
            return True if self.cursor.rowcount > 0 else False
        except sqlite3.Error as e:
            print(f"删除歌曲时出错: {e}")
            self.conn.rollback()
            return False
        finally:
            self.disconnect()

# 实用工具函数
def calculate_file_hash(file_path, block_size=65536):
    """计算文件的MD5哈希值"""
    hasher = hashlib.md5()
    try:
        with open(file_path, 'rb') as file:
            buf = file.read(block_size)
            while len(buf) > 0:
                hasher.update(buf)
                buf = file.read(block_size)
        return hasher.hexdigest()
    except Exception as e:
        print(f"计算文件哈希值时出错: {e}")
        return None